﻿using System;
using System.Linq;
using System.Data;
using System.Linq.Expressions;

namespace Manoso.Data.FluentMap
{
    public interface IMapContext
    {
        /// <summary>
        /// Save all objects using the given map defined.
        /// </summary>
        /// <typeparam name="T">Type of root objects.</typeparam>
        /// <param name="root">root relation.</param>
        void Save<T>(IFluentRoot<T> root);

        /// <summary>
        /// Read all objects for the relations defined.
        /// </summary>
        /// <typeparam name="T">Type of root objects.</typeparam>
        /// <param name="parameters">Anonymous object defined the store procedure need to be executed.</param>
        /// <param name="root">Root relation.</param>
        void Load<T>(IFluentRoot<T> root, object parameters);

        /// <summary>
        /// Auto load objects from db using the given map.
        /// </summary>
        /// <typeparam name="T">Type of the map root.</typeparam>
        /// <param name="root">Root of the map.</param>
        /// <param name="where">The criterion of extracted data.</param>
        void Auto<T>(IFluentRoot<T> root, Expression<Func<T, bool>> where = null);
    }

    internal class Column
    {
        public string Name { get; set; }
        public Type Type { get; set; }
    }

    public class MapContext : IMapContext
    {
        #region Constants

        private const string PREFIX_SYSTEM = "_";
        private const string STORE_PROCEDURE = "_StoreProcedure";
        private const string COMMAND = "_Command";
        private const string TIME_OUT = "_Timeout";
        private const string OUT_PARAMETER = "OutParameter";
        private const string OUT_PARAMETER_VALUE = "Value";
        private const string OUT_PARAMETER_SIZE = "Size";
        private const string BYTES = "Byte[]";
        private const string BINARY = "Binary";

        #endregion

        #region Properties

        private readonly IMapAdaptor _adaptor;
        private readonly IDbConnection _connection;
        private readonly IDbTransaction _transaction;

        private IDataReader _reader;
        private Column[] _columns;

        #endregion

        #region Constructors

        public MapContext(IMapAdaptor adaptor, IDbConnection connection, IDbTransaction transaction = null)
        {
            _adaptor = adaptor;
            _connection = connection;
            _transaction = transaction;
        }

        #endregion

        #region Public Methods

        public void Load<T>(IFluentRoot<T> root, object parameters)
        {
            if (parameters == null) return;

            var mr = root as FluentRoot<T>;
            if (mr == null) return;
            var task = new MapTask { Adaptor = _adaptor, HasColumn = HasColumn, ReadColumn = ReadColumn };

            Read(() => mr.Read(task), parameters);
        }

        public void Save<T>(IFluentRoot<T> root)
        {
            var mr = root as FluentRoot<T>;
            if (mr == null) return;
            var task = new MapTask {Adaptor = _adaptor, Submit = Submit, HasColumn = HasColumn, ReadColumn = ReadColumn};
            mr.Save(task);
        }

        public void Auto<T>(IFluentRoot<T> root, Expression<Func<T, bool>> where = null)
        {
            var fluentRoot = root as FluentRoot<T>;
            if (fluentRoot == null) return;

            var task = new MapTask { Adaptor = _adaptor, HasColumn = HasColumn, ReadColumn = ReadColumn };
            var command = fluentRoot.Auto(task, where);
            if (string.IsNullOrWhiteSpace(command)) return;

            Read(() => fluentRoot.Read(task), new
            {
                _Command = command
            });
        }

        #endregion

        #region Private Methods

        private bool HasColumn(string column)
        {
            var lowerName = column.ToLower();
            return _columns != null && _columns.Any(c => c.Name.ToLower() == lowerName);
        }

        private object ReadColumn(string column)
        {
            return _reader[column];
        }

        private void SetColumns()
        {
            var count = _reader.FieldCount;

            if (count <= 0 || _reader == null) return;
            _columns = new Column[count];
            var schema = _reader.GetSchemaTable();
            if (schema == null) return;

            var rows = schema.Rows;

            for (var i = 0; i < count; i++)
            {
                _columns[i] = new Column
                    {
                        Name = rows[i]["ColumnName"].ToString(),
                        Type = rows[i]["DataType"] as Type
                    };
            }
        }

        private void Read(Action action, object parameters)
        {
            if (parameters == null) return;

            var dbCommand = GetCommand(parameters);
            Read(action, dbCommand);

            _columns = null;
        }

        private void Read(Action action, IDbCommand dbCommand)
        {
            using (var reader = ExecuteReader(dbCommand))
            {
                do
                {
                    SetColumns();

                    while (reader.Read())
                    {
                        action();
                    }
                } while (reader.NextResult());
            }
        }

        private IDbCommand GetCommand(object param, bool hasReturn = false)
        {
            if (param == null) return null;

            var type = param.GetType();
            var spProp = type.GetProperty(STORE_PROCEDURE);
            var spName = spProp == null ? null : spProp.GetValue(param, null) as string;
            var cmProp = type.GetProperty(COMMAND);
            var cmText = cmProp == null ? null : cmProp.GetValue(param, null) as string;

            if (spName == null && cmText == null) return null;

            var toProp = type.GetProperty(TIME_OUT);
            var timeout = toProp == null ? int.MinValue : (int)toProp.GetValue(param, null);

            var paramProps = type.GetProperties().Where(p => !p.Name.StartsWith(PREFIX_SYSTEM)).ToArray();
            IDbCommand cmd;
            if (spName != null)
            {
                cmd = GetStoreProcedure(spName);
                foreach (var p in paramProps ) AddParameter(cmd, p.Name, p.PropertyType, p.GetValue(param, null));

                if (hasReturn)
                    AddReturnParameter(cmd);
            }
            else
            {
                cmd = GetSqlCommand(cmText);
            }
            if (timeout > 0)
                cmd.CommandTimeout = timeout;

            return cmd;
        }

        private void AddParameter(IDbCommand cmd, string name, Type type, object value, int size = 0)
        {
            DbType dbType;

            if (size > 0)
            {
                dbType = GetDbType(type);
                AddOutParameter(cmd, _adaptor.ParameterPrefix + name, dbType, size);
            }
            else if (type.Name.StartsWith(OUT_PARAMETER))
            {
                dbType = GetDbType(type.GetProperty(OUT_PARAMETER_VALUE).PropertyType);
                AddOutParameter(cmd, _adaptor.ParameterPrefix + name, dbType, (int)type.GetProperty(OUT_PARAMETER_SIZE).GetValue(value, null));
            }
            else
            {
                dbType = GetDbType(type);
                AddInParameter(cmd, _adaptor.ParameterPrefix + name, dbType, value);
            }
        }

        private DbType GetDbType(Type type)
        {
            return (DbType)Enum.Parse(typeof(DbType), type.Name.Replace(BYTES, BINARY));
        }

        private IDbCommand GetSqlCommand(string cmd)
        {
            var dbCommand = _connection.CreateCommand();
            dbCommand.CommandText = cmd;
            dbCommand.CommandType = CommandType.Text;
            if (_transaction != null && _transaction.Connection == _connection)
                dbCommand.Transaction = _transaction;
            return dbCommand;
        }

        private IDbCommand GetStoreProcedure(string cmd)
        {
            var dbCommand = _connection.CreateCommand();
            dbCommand.CommandText = cmd;
            dbCommand.CommandType = CommandType.StoredProcedure;
            if (_transaction != null && _transaction.Connection == _connection)
                dbCommand.Transaction = _transaction;
            return dbCommand;
        }

        private IDataReader ExecuteReader(IDbCommand cmd)
        {
            return _reader = cmd.ExecuteReader();
        }

        private void AddReturnParameter(IDbCommand dbCommand)
        {
            var parameter = dbCommand.CreateParameter();
            parameter.ParameterName = _adaptor.ReturnValue;
            parameter.DbType = DbType.Int32;
            parameter.Direction = ParameterDirection.ReturnValue;
            parameter.SourceVersion = DataRowVersion.Default;
            parameter.Value = DBNull.Value;

            dbCommand.Parameters.Add(parameter);
        }

        private void AddOutParameter(IDbCommand dbCommand, string name, DbType dbType, int size)
        {
            var parameter = dbCommand.CreateParameter();
            parameter.DbType = dbType;
            parameter.Size = size;
            parameter.ParameterName = name;
            parameter.Direction = ParameterDirection.Output;

            dbCommand.Parameters.Add(parameter);
        }

        private void AddInParameter(IDbCommand dbCommand, string name, DbType dbType, object value)
        {
            var parameter = dbCommand.CreateParameter();
            parameter.DbType = dbType;
            parameter.ParameterName = name;
            parameter.Direction = ParameterDirection.Input;
            parameter.Value = value;

            dbCommand.Parameters.Add(parameter);
        }

        private void Submit(string command, Action action)
        {
            var sqlCmd = GetSqlCommand(command);
            if (action != null)
                Read(action, sqlCmd);
            else
                ExecuteNonQuery(sqlCmd);
        }

        private void ExecuteNonQuery(IDbCommand dbCommand)
        {
            if (dbCommand == null)
                return;

            dbCommand.ExecuteNonQuery();
        }
   
        #endregion
    }
}
